#!/bin/bash
source /etc/profile
if [ -n "$1" ] ;then
do_date=$1
else
do_date=`date -d "-1 day" +%F`
fi
sql="
insert overwrite table test.shop_his
select shopId , userId, areaId,shopName, shopLv, status,createTime, modifyTime ,modifyTime as start_date, '9999-12-31' as end_date
from test.shop_info
where modifyTime='$do_date'
union all
select B.shopId , B.userId, B.areaId,B.shopName, B.shopLv, B.status,B.createTime, B.modifyTime, B.start_date,
case when B.end_date='9999-12-31' and A.shopId is not null
then date_add('$do_date', -1)
else B.end_date
end as end_date
from (select * from test.shop_info where modifyTime='$do_date') A
right join test.shop_his B
on  A.shopId=B.shopId;
"
hive -e "$sql"
